package io;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import model.App;

import util.JDBCHelper;




public class JDBCApp
{

	private String insertQuery = null;
	private String updateQuery = null;
	private String deleteQuery = null;
	private String findAllQuery = null;
	private String findByIdQuery = null;
	private StringBuilder sb = null;
	
	public JDBCApp()
	{
		sb = new StringBuilder();
		createInsertQuery();
		createUpdateQuery();
		createDeleteQuery();	
		createFindAllQuery();
		createFindByIDQuery();
	}
	
	private void createUpdateQuery()
	{
		StringBuilder sb = new StringBuilder();
		sb.append("UPDATE App ");
		sb.append("SET name=?, category=?, price=?, releaseDate=?");
		updateQuery = sb.toString();
	}

	private void createInsertQuery()
	{
		StringBuilder sb = new StringBuilder();
		sb.append("INSERT INTO App ( name, category, price, releaseDate)" );
		sb.append("VALUES (?, ?, ?, ? );");
		insertQuery = sb.toString();
	}
	
	private void createDeleteQuery()
	{
		StringBuilder sb = new StringBuilder();
		sb.append("DELETE from App ");
		sb.append("WHERE (id = ?)");
		deleteQuery = sb.toString();
	}
	
	private void createFindAllQuery()
	{
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT id, name, category, price, releaseDate ");
		sb.append("FROM App");
		findAllQuery = sb.toString();
	}
	
	private void createFindByIDQuery()
	{
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT name, category, price, releaseDate ");
		sb.append("FROM App ");
		sb.append("WHERE id = ?");
		findByIdQuery = sb.toString();
	}

	public int insert(Connection con, App data) throws IOException
	{
		int rows = 0;
		PreparedStatement stm = null;
		
		try
		{
			stm = con.prepareStatement(insertQuery, PreparedStatement.RETURN_GENERATED_KEYS);
			stm.setString(1, data.getName());
			stm.setString(2, data.getCategory());
			stm.setDouble(3, data.getPrice());
			stm.setTimestamp(4, JDBCHelper.toSqlTimestamp(data.getReleaseDate()));
		
			rows = stm.executeUpdate();
			ResultSet rs = stm.getGeneratedKeys();
			if(rs.next())
			{
				data.setID(rs.getLong(1));
			}
			
			rs.close();
            stm.close();
		} catch (SQLException e)
		{
			throw new IOException("Insert error", e);
		}
		return rows;
	}
	
	public int update(Connection con, App data) throws IOException
	{
		checkConnection(con);
		checkData(data);
		PreparedStatement stm = null;
			
		int rows = 0;
		try
		{
			stm = con.prepareStatement(updateQuery);
			stm.setString(1, data.getName());
			stm.setString(2, data.getCategory());
			stm.setDouble(3, data.getPrice());
			stm.setTimestamp(4, JDBCHelper.toSqlTimestamp(data.getReleaseDate()));
			System.out.println("update");
			rows = stm.executeUpdate();
		} catch (SQLException e)
		{
			throw new IOException("updateError",e);
		}
		return rows;
	}
	
	
	public int delete(Connection con, Long id) throws IOException 
	{
		checkConnection(con);
		
		int rows = 0;
		try
		{
			PreparedStatement stm = con.prepareStatement(deleteQuery);
			stm.setLong(1, id);
			rows = stm.executeUpdate();
			
		} catch (SQLException e)
		{
			throw new IOException("deleteError", e);
		}
		
		return rows;
	}

	
	public ArrayList<App> findAll(Connection con) throws IOException
	{
		checkConnection(con);
		
		ArrayList<App> apps = null;
		App a = null;
		
		try
		{

			PreparedStatement stm = con.prepareStatement(findAllQuery);
			ResultSet rs = stm.executeQuery();
			apps = new ArrayList<App>();
			
			while(rs.next())
			{
				a = new App(rs.getString("name"));
				a.setID(rs.getLong("id"));
				a.setCategory(rs.getString("category"));
				a.setPrice(rs.getDouble("price"));
				a.setTimeStamp(JDBCHelper.toDate(rs.getDate("releaseDate")));
				
				apps.add(a);
			}
		} catch (SQLException e)
		{
			throw new IOException("findError", e);
		}
		return apps;
	}
	
	

	public App findByID(Connection con, Long id) throws IOException 
	{
		checkConnection(con);
		if(id == null)throw new IllegalArgumentException("ID must not be null");
		
		App a = null;
				
		try
		{
			PreparedStatement stm = con.prepareStatement(findByIdQuery);
			stm.setLong(1, id);
			ResultSet rs = stm.executeQuery();
						
			while(rs.next())
			{
				a = new App(rs.getString("name"));
				a.setID(id);
				a.setCategory(rs.getString("category"));
				a.setPrice(rs.getDouble("price"));
				a.setReleaseDate(rs.getTimestamp("releaseDate"));
			}
			rs.close();
		} catch (SQLException e)
		{
			throw new IOException("findByIDError", e);
		}
		return a;
	}

	
	public void checkConnection(Connection con)
	{
		if(con == null) throw new IllegalArgumentException();
	}
	
	public void checkData(App data)
	{
		if(data == null) throw new IllegalArgumentException();
	}
}
